<?php
/**
 * Created by PhpStorm.
 * User: liqiang
 * Date: 16-12-29
 * Time: 下午2:47
 */

namespace Inbound\Service;

use Home\Service\CommonService;
use Think\Model;

//use Inbound\Model\TransportplanModel;

class TransportplanService extends CommonService
{
    /**
     * 添加数据
     * @param  array $data  需要添加的数据
     * @param  obj   $model 事务对象
     * @return array
     */
    public function createtransplan($_str = '',&$model = NULL)
    {
        //$platplan_arr=explode(',', $_array);
        //var_dump($platplan_arr);exit;
        $createtrans = D('Transportplan')->createTransplans($_str);
        return $createtrans;
    }

    /**
     * 物流计划单标记发货
     * @param  array $where 条件     更新的条件
     * @param  array $array 更新的数据
     * @param        $model $model    事务
     * @return array
     */
    public function update($where,$array,&$model = NULL)
    {
        $data['pickup_op_user_id'] = $_SESSION['current_account']['id'];
        $data['status']            = 60;
        $result                    = D('Transportplan')->editTransPlan($where,$data);
        return $result;
    }

    /**
     * 更新数据
     * @param  array $where 条件     更新的条件
     * @param  array $array 更新的数据
     * @param        $model $model    事务
     * @return array
     */
    public function delplat($where)
    {
        $data['status']            = 40;
        $data['transport_plan_id'] = 0;
        $result                    = D('Transportplan')->upplat($where,$data);
        return $result;
    }

    /**
     * 取消物流计划单
     * @param  array $where 条件     更新的条件
     * @return array
     */
    public function cancelTransPlan($where)
    {
        $where_p['transport_plan_id'] = $where['id'];
        $data['status']               = 40;
        $data['transport_plan_id']    = 0;
        $data_t['status']             = 100;
        $result                       = D('Transportplan')->upplat($where_p,$data,$data_t);
        return $result;
    }

    /**
     * 查询数据
     * @param  param $where 条件     查询的条件
     * @param  BOOL  $flag  是否分页 true 是
     * @return array
     */
    public function getPlatplans($param = array(),$flag = TRUE)
    {
        //return $this->TransplanModel->select();
        $where = array();
        //$where['a.id'] = '`b.inbound_shipment_plan_i`d';
        if(!empty($param['site_id'])){
            $where['site_id'] = $param['site_id'];
        }
        if(!empty($param['carrier_service'])){
            $where['a.carrier_service_id'] = $param['carrier_service'];
        }
        if($param['taxrebate']==='1'){//'rebate'
            $where['b.export_tax_rebate'] = 1;
        }elseif($param['taxrebate']==='0'){//'norebate'
            $where['b.export_tax_rebate'] = 0;
        }
        if(!empty($param['start_time'])&&!empty($param['end_time'])){
            $where[$param['datetype']] = array(
                'between',
                array(
                    $param['start_time'],
                    $param['end_time']
                )
            );
        }elseif(!empty($param['start_time'])&&empty($param['end_time'])){
            $where[$param['datetype']] = array(
                'EGT',
                $param['start_time']
            );
        }elseif(empty($param['start_time'])&&!empty($param['end_time'])){
            $where[$param['datetype']] = array(
                'ELT',
                $param['end_time']
            );
        }
        $where['a.status'] = 40;
        $trans             = D('Transportplan')->getPlatplans($where);
        //var_dump($param);exit;
        return $trans;
    }

    /**
     * 核对包裹
     * @param  array $where 条件
     * @return array
     */
    public function verify_prepackage($array = array())
    {
        return TRUE;
    }

    /**
     * 获取平台计划详情
     * @param  array $where 条件
     * @return array
     */
    public function get_platform_detail($where)
    {
        return TRUE;
    }

    /**
     * 获取箱唛详情
     * @param  array $where 条件
     * @return array
     */
    public function get_package_detail()
    {
        return TRUE;
    }

    /**
     * 修改物流计划详情
     * @param  array $param 传入参数
     * @return array
     */
    public function editTrans($param)
    {
        //return $this->TransplanModel->select();
        $where = array();
        //$where = '1=1';
        if(!empty($param['id'])){
            $where['id'] = $param['id'];
        }
        if(!empty($param['status'])){
            $data['status'] = $param['status'];
        }
        if(!empty($param['certificate_code'])){
            $data['certificate_code'] = $param['certificate_code'];
        }
        if(!empty($param['carrier_service_id'])){
            $data['carrier_service_id'] = $param['carrier_service_id'];
        }
        if(!empty($param['delivery_proxy'])){
            $data['delivery_proxy'] = $param['delivery_proxy'];
        }
        if(!empty($param['ori_driver_name'])){
            $data['ori_driver_name'] = $param['ori_driver_name'];
        }
        if(!empty($param['ori_driver_tel'])){
            $data['ori_driver_tel'] = $param['ori_driver_tel'];
        }
        if(!empty($param['ori_license_plate_num'])){
            $data['ori_license_plate_num'] = $param['ori_license_plate_num'];
        }
        if(!empty($param['ori_tracking_num'])){
            $data['ori_tracking_num'] = $param['ori_tracking_num'];
        }
        if(!empty($param['certificate_code'])){
            $data['certificate_code'] = $param['certificate_code'];
        }
        if(!empty($param['ori_seal_num'])){
            $data['ori_seal_num'] = $param['ori_seal_num'];
        }
        if(!empty($param['transport_supplier_id'])){
            $data['transport_supplier_id'] = $param['transport_supplier_id'];
        }
        if(!empty($param['expected_pickup_time'])){
            $data['expected_pickup_time'] = $param['expected_pickup_time'];
        }
        if(!empty($param['pickup_op_user_id'])){
            $data['pickup_op_user_id'] = $param['pickup_op_user_id'];
        }
        if(!empty($param['pickup_op_time'])){
            $data['pickup_op_time'] = $param['pickup_op_time'];
        }
        if(!empty($param['arrive_port_date'])){
            $data['arrive_port_date'] = $param['arrive_port_date'];
        }
        if(!empty($param['take_off_date'])){
            $data['take_off_date'] = $param['take_off_date'];
        }
        if(!empty($param['arrive_time'])){
            $data['arrive_time'] = $param['arrive_time'];
        }
        if(!empty($param['customs_clearance_date'])){
            $data['customs_clearance_date'] = $param['customs_clearance_date'];
        }
        if(!empty($param['delivery_date'])){
            $data['delivery_date'] = $param['delivery_date'];
        }
        if(!empty($param['destination_site_id'])){
            $data['destination_site_id'] = $param['destination_site_id'];
        }
        if(!empty($param['clearance_port'])){
            $data['clearance_port'] = $param['clearance_port'];
        }
        $trans = D('Transportplan')->editTransPlan($where,$data);
        return $trans;
    }

    public function getTransportplan($param,$limit = '')
    {
        //return $this->TransplanModel->select();
        $where = array();
        //$where = '1=1';
        if(!empty($param['transcode'])){
            $where[$param['codetype']] = $param['transcode'];
        }
        if(!empty($param['carrier_service'])){
            $where['a.carrier_service_id'] = $param['carrier_service'];
        }
//        if(!empty($param['creater'])){
//            $where['a.create_user_id'] = $param['creater'];
//        }
        if(!empty($param['status'])){
            $where['a.status'] = $param['status'];
        }
        if(!empty($param['start_time'])&&!empty($param['end_time'])){
            $where['a.'.$param['datetype']] = array(
                'between',
                array(
                    $param['start_time'],
                    $param['end_time']
                )
            );
        }elseif(!empty($param['start_time'])&&empty($param['end_time'])){
            $where['a.'.$param['datetype']] = array(
                'EGT',
                $param['start_time']
            );
        }elseif(empty($param['start_time'])&&!empty($param['end_time'])){
            $where['a.'.$param['datetype']] = array(
                'ELT',
                $param['end_time']
            );
        }
        if(!empty($param['id'])){
            $where['a.id'] = $param['id'];
        }
        $trans = D('Transportplan')->select($where,$limit);
        //var_dump(D('Transportplan')->getLastsql());exit;
        return $trans;
    }
    public function getTransportplanOther($param)
    {
        //return $this->TransplanModel->select();
        $where = array();
        //$where = '1=1';
        if(!empty($param['transcode'])){
            $where[$param['codetype']] = $param['transcode'];
        }
        if(!empty($param['carrier_service'])){
            $where['ftp.carrier_service_id'] = $param['carrier_service'];
        }
//        if(!empty($param['creater'])){
//            $where['ftp.create_user_id'] = $param['creater'];
//        }
        if(!empty($param['status'])){
            $where['ftp.status'] = $param['status'];
        }
        if(!empty($param['start_time'])&&!empty($param['end_time'])){
            $where['ftp.'.$param['datetype']] = array(
                'between',
                array(
                    $param['start_time'],
                    $param['end_time']
                )
            );
        }elseif(!empty($param['start_time'])&&empty($param['end_time'])){
            $where['ftp.'.$param['datetype']] = array(
                'EGT',
                $param['start_time']
            );
        }elseif(empty($param['start_time'])&&!empty($param['end_time'])){
            $where['ftp.'.$param['datetype']] = array(
                'ELT',
                $param['end_time']
            );
        }
        if(!empty($param['id'])){
            $where['ftp.id'] = $param['id'];
        }
        $trans = D('Transportplan')->selectOther($where);
        //var_dump(D('Transportplan')->getLastsql());exit;
        return $trans;
    }

    public function getTransplan($param)
    {
        //return $this->TransplanModel->select();
        $where = array();
        //$where = '1=1';
        if(!empty($param['transcode'])){
            $where['transport_plan_code'] = $param['transcode'];
        }
        if(!empty($param['creater'])){
            $where['create_user_id'] = $param['creater'];
        }
        if(!empty($param['status'])){
            $where['status'] = $param['status'];
        }
        if(!empty($param['start_time'])&&!empty($param['end_time'])){
            $where[$param['datetype']] = array(
                'between',
                array(
                    $param['start_time'],
                    $param['end_time']
                )
            );
        }elseif(!empty($param['start_time'])&&empty($param['end_time'])){
            $where[$param['datetype']] = array(
                'EGT',
                $param['start_time']
            );
        }elseif(empty($param['start_time'])&&!empty($param['end_time'])){
            $where[$param['datetype']] = array(
                'ELT',
                $param['end_time']
            );
        }
        if(!empty($param['id'])){
            $where['id'] = $param['id'];
        }
        $trans = D('Transportplan')->getTransplan($where);
        //var_dump($trans);exit;
        return $trans;
    }

    public function getCarrierServiceList($param,$limit = '')
    {
        $trans = D('Transportplan')->getCarrierServiceList();
        //var_dump($trans);exit;
        return $trans;
    }
    /**
     * 描述: 检查导入合法性
     * 作者: kelvin
     */
    public function check_upload_data(&$datas, $status = 0)
    {
        if ($status) {
            $saleStatus = PublicInfoService::getSaleStatusName();
            $saleStatusValue = array_flip($saleStatus);
        }
        $model = M('','','fbawarehouse');
        $flag = true;
        $str  = '';
        $ysfs = array('空运','快递','海运','TNT','铁运');
        foreach ($datas as $k=>&$v){
            if($k==1) continue;
            if(trim($v['A'])==''){
                $flag = false;
                $str .= '第'.$k.'行 shipmentid 为空,导入失败\n';
            }else{
                $ship =  $model->table('fba_inbound_shipment_plan')->where("shipmentid = '".trim($v['A'])."' AND status BETWEEN 50 AND 70 ")->getField('id');
                if(!$ship){
                    $flag = false;
                    $str .= '第'.$k.'行 shipmentid:'.trim($v['A']).'的不在物流计划单中,导入失败\n';
                }
            }
            if(trim($v['B'])==''){
                $flag = false;
                $str .= '第'.$k.'行 追踪号 为空,导入失败\n';
            }
            if(trim($v['C'])==''){
                $flag = false;
                $str .= '第'.$k.'行 总运费 为空,导入失败\n';
            }
            if(trim($v['D'])==''){
                $flag = false;
                $str .= '第'.$k.'行 运输方式 为空,导入失败\n';
            }
            if(!in_array(trim($v['D']),$ysfs)){
                $flag = false;
                $str .= '第'.$k.'行 运输方式 '.$v['D'].' 不正确,导入失败\n';
            }

        }
        return array(
            'status' => $flag,
            'message'=> $str
        );
    }
    /**
     * 描述: 上传下载
     * 作者: kelvin
     */
    public function upload_platform($arr){
        set_time_limit(0);
        ini_set('memory_limit','2048M');
        $site = PublicInfoService::get_site_array();
        $data = array();
        $model = M('','','fbawarehouse');
        foreach($arr as $a =>$r){
            if($a >= 2){
                $data[trim($r['A'])]['shipmentid'] =  trim($r['A']);
                $data[trim($r['A'])]['code'] =  trim($r['B']);
                $data[trim($r['A'])]['price'] =  trim($r['C']);
                $data[trim($r['A'])]['service'] =  trim($r['D']);
            }
        }
        $shipmentId = implode("','",array_keys($data));
        $sql = "SELECT a.shipmentid,a.account_name,a.site_id,a.transport_plan_id,a.destination_fullfillment_center_id,c.create_time,
            p.id AS 'box_id',p.length,p.width,p.height,(p.length*p.width*p.height) as 'volume',p.weight, p.export_tax_rebate,c.pickup_time,
            s.name as 'service_name'
            FROM `fba_inbound_shipment_plan` AS a 
            LEFT JOIN  `fba_transport_plan` AS c ON c.id = a.transport_plan_id
            LEFT JOIN `fba_package_box` AS p ON p.inbound_shipment_plan_id = a.id
            LEFT JOIN `fba_carrier` as s ON  s.id = c.carrier_service_id
            WHERE a.shipmentid IN ('$shipmentId')
            ORDER BY  c.create_time ASC,shipmentid asc,p.id asc ";
        $result = $model->query($sql);
        $shipmentAll = array();
        //获取shipmentid总体积和总重量
        foreach ($result as $a =>$b){
            $shipmentAll[$b['shipmentid']]['allVolume'] += $b['volume'];
            $shipmentAll[$b['shipmentid']]['allWeight'] += $b['weight'];
            $shipmentAll[$b['shipmentid']]['allBox'] += 1;
            $shipmentAll[$b['shipmentid']]['boxid'][$b['box_id']] = $b['box_id'];
        }
        $box = array();
        foreach ($shipmentAll as $k =>$v){
            $num = 0;
            foreach ($v['boxid'] as $b =>$t){
                $num += 1;
                $box[$b] = $num.'/'.$v['allBox'];
            }

        }
        foreach ($result as $s =>$t){
            $one = 0;
            if(trim($data[$t['shipmentid']]['service'])=='海运'){
                $one = $t['volume']/1000000;
            }
            if(trim($data[$t['shipmentid']]['service'])=='空运' || trim($data[$t['shipmentid']]['service'])=='TNT' || trim($data[$t['shipmentid']]['service'])=='铁运'){
                if(trim($data[$t['shipmentid']]['service'])=='TNT'){
                    if(($shipmentAll[$t['shipmentid']]['allVolume']/5000)>$shipmentAll[$t['shipmentid']]['allWeight']){
                        $one = $t['volume']/5000;
                    }else{
                        $one = $t['weight'];
                    }
                }else{
                    if(($shipmentAll[$t['shipmentid']]['allVolume']/6000)>$shipmentAll[$t['shipmentid']]['allWeight']){
                        $one = $t['volume']/6000;
                    }else{
                        $one = $t['weight'];
                    }
                }

            }
            if(trim($data[$t['shipmentid']]['service'])=='快递'){
                if($t['weight']>($t['volume']/5000)){
                    $one = $t['weight'];
                }else{
                    $one = $t['volume']/5000;
                }
            }
            $shipmentAll[$t['shipmentid']]['allPrice'] += $one;

        }
        vendor('PHPExcel.PHPExcel');
        $PHPExcel = new \PHPExcel();
        //$PHPReader = new \PHPExcel_Reader_Excel2007();
        $PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $PHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $currentSheet = $PHPExcel->getSheet(0);
        $currentSheet->setCellValue('A1','发货日期');
        $currentSheet->setCellValue('B1','shipmentid');
        $currentSheet->setCellValue('C1', '账号');
        $currentSheet->setCellValue('D1', '站点');
        $currentSheet->setCellValue('E1', '追踪号');
        $currentSheet->setCellValue('F1', '目的仓');
        $currentSheet->setCellValue('G1', '箱号');
        $currentSheet->setCellValue('H1', 'sku');
        $currentSheet->setCellValue('I1', 'sku中文名');
        $currentSheet->setCellValue('J1', '是否退税');
        $currentSheet->setCellValue('K1', '主体');
        $currentSheet->setCellValue('L1', '数量');
        $currentSheet->setCellValue('M1', '实重(kg)');
        $currentSheet->setCellValue('N1', '尺寸(长/宽/高)');
        $currentSheet->setCellValue('O1', '体积(m3)');
        $currentSheet->setCellValue('P1', '计费重');
        $currentSheet->setCellValue('Q1', '平摊费用');
        $currentSheet->setCellValue('R1', '总费用');
        $currentSheet->setCellValue('S1', '运输方式');
        $currentSheet->setCellValue('T1', '渠道');
        $k = 2;
        foreach ($result as $y =>$v){
            $boxDetail = $this->findSkuByBoxId($v['box_id']);
            $onePrice = 0;
            if(trim($data[$v['shipmentid']]['service'])=='海运'){
                $onePrice = $v['volume']/1000000;
            }
            if(trim($data[$v['shipmentid']]['service'])=='空运' || trim($data[$v['shipmentid']]['service'])=='TNT' || trim($data[$v['shipmentid']]['service'])=='铁运'){
                if(trim($data[$v['shipmentid']]['service'])=='TNT'){
                    if(($shipmentAll[$v['shipmentid']]['allVolume']/5000)>$shipmentAll[$v['shipmentid']]['allWeight']){
                        $onePrice = $v['volume']/5000;
                    }else{
                        $onePrice = $v['weight'];
                    }
                }else{
                    if(($shipmentAll[$v['shipmentid']]['allVolume']/6000)>$shipmentAll[$v['shipmentid']]['allWeight']){
                        $onePrice = $v['volume']/6000;
                    }else{
                        $onePrice = $v['weight'];
                    }
                }

            }
            if(trim($data[$v['shipmentid']]['service'])=='快递'){
                if($v['weight']>($v['volume']/5000)){
                    $onePrice = $v['weight'];
                }else{
                    $onePrice = $v['volume']/5000;
                }
            }
            $banPrice = ($data[$v['shipmentid']]['price']/$shipmentAll[$v['shipmentid']]['allPrice'])*$onePrice;
//            $currentSheet->setCellValue('A'.$k,$v['pickup_time']);
//            $currentSheet->setCellValue('B'.$k,$v['shipmentid']);
//            $currentSheet->setCellValue('C'.$k,$v['account_name']);
//            $currentSheet->setCellValue('D'.$k,$v['site_id']);
//            $currentSheet->setCellValue('E'.$k,$data[$v['shipmentid']]['code']);
//            $currentSheet->setCellValue('F'.$k,$v['destination_fullfillment_center_id']);
//            $currentSheet->setCellValue('G'.$k,$v['box_id']);
            foreach ($boxDetail as $s =>$u){
                if($s==0){
                    $currentSheet->setCellValue('A'.$k,$v['create_time']);
                    $currentSheet->mergeCells('A'.$k.':A'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('B'.$k,$v['shipmentid']);
                    $currentSheet->mergeCells('B'.$k.':B'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('C'.$k,$v['account_name']);
                    $currentSheet->mergeCells('C'.$k.':C'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('D'.$k,$site[$v['site_id']]);
                    $currentSheet->mergeCells('D'.$k.':D'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('E'.$k,$data[$v['shipmentid']]['code']);
                    $currentSheet->mergeCells('E'.$k.':E'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('F'.$k,$v['destination_fullfillment_center_id']);
                    $currentSheet->mergeCells('F'.$k.':F'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('G'.$k,$box[$v['box_id']]);
                    $currentSheet->mergeCells('G'.$k.':G'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('M'.$k,$v['weight']);
                    $currentSheet->mergeCells('M'.$k.':M'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('N'.$k,$v['length'].'*'.$v['width'].'*'.$v['height']);
                    $currentSheet->mergeCells('N'.$k.':N'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('O'.$k,$v['volume']/1000000);
                    $currentSheet->mergeCells('O'.$k.':O'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('P'.$k,$onePrice);
                    $currentSheet->mergeCells('P'.$k.':P'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('Q'.$k,$banPrice);
                    $currentSheet->mergeCells('Q'.$k.':Q'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('R'.$k,$data[$v['shipmentid']]['price']);
                    $currentSheet->mergeCells('R'.$k.':R'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('S'.$k,$data[$v['shipmentid']]['service']);
                    $currentSheet->mergeCells('S'.$k.':S'.(count($boxDetail)+$k-1));
                    $currentSheet->setCellValue('T'.$k,$v['service_name']);
                    $currentSheet->mergeCells('T'.$k.':T'.(count($boxDetail)+$k-1));
                }
//                $currentSheet->setCellValue('A'.$k,$v['create_time']);
//                $currentSheet->setCellValue('B'.$k,$v['shipmentid']);
//                $currentSheet->setCellValue('C'.$k,$v['account_name']);
//                $currentSheet->setCellValue('D'.$k,$site[$v['site_id']]);
//                $currentSheet->setCellValue('E'.$k,$data[$v['shipmentid']]['code']);
//                $currentSheet->setCellValue('F'.$k,$v['destination_fullfillment_center_id']);
//                $currentSheet->setCellValue('G'.$k,$box[$v['box_id']]);
                $currentSheet->setCellValue('H'.$k,$u['sku']);
                $currentSheet->setCellValue('I'.$k,$u['sku_name']);
                $currentSheet->setCellValue('J'.$k,$v['export_tax_rebate']==1?'退税':'非退税');
                $currentSheet->setCellValue('K'.$k,$this->getEnterprise($u['inbound_shipment_plan_id'],$u['sku']));
                $currentSheet->setCellValue('L'.$k,$u['quantity']);

//                $currentSheet->setCellValue('M'.$k,$v['weight']);
//                $currentSheet->setCellValue('N'.$k,$v['length'].'*'.$v['width'].'*'.$v['height']);
//                $currentSheet->setCellValue('O'.$k,$v['volume']/1000000);

//                $currentSheet->setCellValue('P'.$k,$onePrice);
//                $currentSheet->setCellValue('Q'.$k,$banPrice);
//                $currentSheet->setCellValue('R'.$k,$data[$v['shipmentid']]['price']);
//                $currentSheet->setCellValue('S'.$k,$data[$v['shipmentid']]['service']);
//                $currentSheet->setCellValue('T'.$k,$v['service_name']);

                $k +=1;
            }


        }
        $PHPWriter = new \PHPExcel_Writer_Excel2007($PHPExcel);
        $outputFileName = 'FBA头程分摊报表'.date('YmdHis').'.xlsx';
        ob_start();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:attachment;filename="' .$outputFileName. '"');//输出模板名称
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified:".gmdate("D, d M Y H:i:s")." GMT");
        header('Pragma: public');
        header('Expires: 60');
        header('Cache-Control: public');
        $PHPWriter->save('php://output');
        die;
    }
    /**
     * 描述: 根据箱号id查询所装sku
     * 作者: kelvin
     */
    public function findSkuByBoxId($boxId) {
        if ($boxId){
            return M('package_box_details','fba_','fbawarehouse')->where("package_box_id = ".$boxId)->select();
        }else{
            return array();
        }

    }
    /**
     * 描述: 根据shipmentid和sku获取主体
     * 作者: kelvin
     */
    public function getEnterprise($id,$sku) {
        $planModel =  M('inbound_shipment_plan_detail', 'fba_', 'fbawarehouse');
        if($id && $sku){
            $where = array(
                'inbound_shipment_plan_id' =>$id,
                'sku' =>$sku,
            );
            $result = $planModel->where($where)->getField('enterprise_dominant');
            return $result?PublicInfoService::getCompanyName($result):null;
        }
    }
    /**
     * 描述：物流交接单上传下载
     * 作者：kelvin
     */
    public function upload_site($data = array())
    {
        if (empty($data)) {
            echo "<script>alert('数据为空');history.go(-1);</script>";die;
        } else {
            set_time_limit(0);
            $where = implode(',',$data);
//            $where['status'] = 60;
//            $where['shipmentid'] = 'FBA4GSG7PK';
            $planModel = M('inbound_shipment_plan', 'fba_', 'DB_FBAERP');
            $users = PublicInfoService::get_user_name_and_id();
            $model = M('','','DB_FBAERP');
            $sql = "SELECT site_id,shipmentid,account_name,seller_id,create_user_id,boxNum,weightAll,volume,
                destination_fullfillment_center_id,money,export_tax_rebate,remark,gro_weight,gro_volume,create_time,picking_time
                FROM
                (SELECT 
                a.id,a.site_id,a.account_name,a.shipmentid,a.destination_fullfillment_center_id ,SUM(b.weight) as weightAll,
                SUM(b.length*b.width*b.height/1000000) as volume,count(1) as boxNum,a.seller_id,a.create_user_id,
                b.export_tax_rebate,a.remark,a.create_time,a.picking_time
                FROM fba_inbound_shipment_plan a,fba_package_box b
                WHERE 
                a.id = b.inbound_shipment_plan_id
                AND
                a.site_id in ($where) 
                AND 
                a.`status` = 40
                GROUP BY a.shipmentid ) aaa
                LEFT JOIN
                (
                SELECT aa.inbound_shipment_plan_id,sum(aa.quantity*bb.num) as money ,sum(aa.quantity*wei.num/1000) as gro_weight,
                sum(len.num*wid.num*hei.num*aa.quantity/1000000000) as gro_volume
                FROM 
                `fba_package_box_details` aa
                LEFT JOIN skusystem_price bb ON bb.sku= aa.sku
                LEFT JOIN fba_inbound_shipment_plan cc ON cc.id = aa.inbound_shipment_plan_id
                LEFT JOIN skusystem_asweight wei ON aa.sku = wei.sku
                LEFT JOIN skusystem_aslength len ON aa.sku = len.sku
                LEFT JOIN skusystem_aswidth wid ON aa.sku = wid.sku
                LEFT JOIN skusystem_asheight hei ON aa.sku = hei.sku
                WHERE
                cc.site_id in ($where) 
                AND 
                cc.`status` = 40
                GROUP BY aa.inbound_shipment_plan_id ) bbb
                ON aaa.id = bbb.inbound_shipment_plan_id
                ORDER BY site_id DESC";
            $result = $model->query($sql);
            if(count($result)<=0){
                echo "<script>alert('数据为空');history.go(-1);</script>";die;
            }
            vendor('PHPExcel.PHPExcel');
            /*模板存放目录*/
            $templateFile = realpath(__ROOT__) . '/Admin/Inbound/Template/' . date('Y-m-d', time());
            $site = PublicInfoService::get_site_array();//站点
            $company = PublicInfoService::get_company_array();//主体
            /*目标投放文件名*/
            $outputFileName = '物流交接单' . date('YmdHis') . '.xlsx';
            $PHPExcel = new \PHPExcel();
            //$PHPReader = new \PHPExcel_Reader_Excel2007();
            $currentSheet = $PHPExcel->getSheet(0);
            $PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            $PHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $currentSheet->getColumnDimension('B')->setWidth(15);
            $currentSheet->getColumnDimension('C')->setWidth(20);
//            $currentSheet->getColumnDimension('E')->setWidth(40);
//            $currentSheet->getColumnDimension('L')->setWidth(35);
            $currentSheet->setCellValue('A1', '站点');
            $currentSheet->setCellValue('B1', 'SHIPMENTID');
            $currentSheet->setCellValue('C1', '账号');
            $currentSheet->setCellValue('D1', "销售人员");
            $currentSheet->setCellValue('E1', "箱数");
            $currentSheet->setCellValue('F1', "实重(KG)");
            $currentSheet->setCellValue('G1', "体积(M³)");
            $currentSheet->setCellValue('H1', "产生订单时间");
            $currentSheet->setCellValue('I1', "订单打印时间");
            $currentSheet->setCellValue('J1', "ERP重量(KG)");
            $currentSheet->setCellValue('K1', "ERP体积(M³)");
            $currentSheet->setCellValue('L1', "目的地国家");
            $currentSheet->setCellValue('M1', "采购货值(￥)");
            $currentSheet->setCellValue('N1', "退税类型");
            $currentSheet->setCellValue('O1', "备注");
            $num = 2;
            foreach ($result as $k => &$v) {
                    $currentSheet->setCellValue('A' . $num, $site[$v['site_id']]);
                    $currentSheet->setCellValue('B' . $num, $v['shipmentid']);
                    $currentSheet->setCellValue('C' . $num, $v['account_name']);
                    $currentSheet->setCellValue('D' . $num, $users[$v['seller_id']?$v['seller_id']:$v['create_user_id']]);
                    $currentSheet->setCellValue('E' . $num, $v['boxNum']);
                    $currentSheet->setCellValue('F' . $num, $v['weightAll']);
                    $currentSheet->setCellValue('G' . $num, $v['volume']);
                    $currentSheet->setCellValue('H' . $num, $v['create_time']);
                    $currentSheet->setCellValue('I' . $num, $v['picking_time']);
                    $currentSheet->setCellValue('J' . $num, $v['gro_weight']);
                    $currentSheet->setCellValue('K' . $num, $v['gro_volume']);
                    $currentSheet->setCellValue('L' . $num, $v['destination_fullfillment_center_id']);
                    $currentSheet->setCellValue('M' . $num, $v['money']);
                    $currentSheet->setCellValue('N' . $num, $v['export_tax_rebate'] == 1 ? '是' : '否');
                    $currentSheet->setCellValue('O' . $num, $v['remark']);
                    $num += 1;

            }
            $PHPWriter = new \PHPExcel_Writer_Excel2007($PHPExcel);
            ob_start();
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            header("Content-Type: application/download");
            header('Content-Disposition:attachment;filename="' . $outputFileName . '"');//输出模板名称
            header("Content-Transfer-Encoding: binary");
            header("Last-Modified:" . gmdate("D, d M Y H:i:s") . " GMT");
            header('Pragma: public');
            header('Expires: 30');
            header('Cache-Control: public');
            $PHPWriter->save('php://output');
            die;
        }
    }
} 